﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;

using System.Data.SqlClient;
using System.Xml;

namespace ConnectionBuilder
{
    public enum enVisible
    {
        Visible = 0,
        Invisible = 1
    }

    public partial class DatabaseType : UserControl
    {
        #region "component delegations"
        /// <summary>
        /// connect button
        /// </summary>
        public delegate void ConnectDelegate(ik.DB.enSGBD sgbd);
        /// <summary>
        /// click connect
        /// </summary>
        public event ConnectDelegate OnConnect;

        /// <summary>
        /// error
        /// </summary>
        public delegate void ErrorDelegate(Exception e);
        /// <summary>
        /// error
        /// </summary>
        public event ErrorDelegate OnError;
        #endregion

        #region "variables public of connection"

        public ik.DB.clDB clDb { get; private set; }

        private static Boolean DEBUGmode = true;
        private static String PathXML = "";
        private static List<String> SGBD { get; set; }

        private static String SQLServer_ConnectionStringUser { get; set; }
        private static String SQLServer_ConnectionStringTrust { get; set; }
        private static String Oracle_ConnectionStringUser { get; set; }

        private List<TabPage> PagesSGBD { get; set; }

        #endregion

        #region "constructor"
        /// <summary>
        /// constructor
        /// </summary>
        public DatabaseType()
        {
            InitializeComponent();

            try
            {
                SetDefaultInformationOnConstructor();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        #endregion

        #region "SetDefaultInformationOnConstructor"
        /// <summary>
        /// set default values on constructor
        /// </summary>
        private void SetDefaultInformationOnConstructor()
        {
            try
            {
                //load exists TabPages list
                PagesSGBD = new List<TabPage>();
                foreach (TabPage tp in tabSGBD.TabPages)
                {
                    PagesSGBD.Add(tp);
                }

                //XML path file with the SGBDs
                if (DEBUGmode)
                    if (Environment.UserName.ToLower().Contains("kruger")) //home ivan
                        PathXML = @"E:\_estudos dotNet\SQLQuery\SQLQuery\IReader_demo\IReader_demo\bin\Debug\DatabaseConfig.xml";
                    else if (Environment.UserName.ToLower().Contains("ivan")) //work ivan
                        PathXML = @"E:\estudos dotNet\SQLQuery\SQLQuery\IReader_demo\IReader_demo\bin\Debug\DatabaseConfig.xml";

                    // Eliel: Preencha aqui o USERNAME da sua maquina com o respectivo PATH
                    else if (Environment.UserName.ToLower().Contains("eliel jungles"))
                        PathXML = @"E:\Simon System\Sistema\SQLQuery\SQLQuery\IReader_demo\IReader_demo\bin\Debug\DatabaseConfig.xml";

                    else
                        PathXML = System.IO.Path.Combine(Application.ExecutablePath, "DatabaseConfig.xml");
                //PathXML = new System.IO.FileInfo(System.Reflection.Assembly.GetEntryAssembly().Location).Directory.ToString() + @"\DatabaseConfig.xml";


                //SGBDs types (sqlserver, oracle, firebird...)
                SetComboInformation(cboDatabaseType, GetSGBD(PathXML), 0);
                //last servers connected
                cnnSQLServer.SetServerNames(GetXmlReader(PathXML, "dbConfigs/ConnectionString/SQLServer_LastServersNames").Split('|'));

                //connection string - sqlserver
                SQLServer_ConnectionStringUser = GetXmlReader(PathXML, "dbConfigs/ConnectionString/SQLServer_ConnectionStringUser");
                SQLServer_ConnectionStringTrust = GetXmlReader(PathXML, "dbConfigs/ConnectionString/SQLServer_ConnectionStringTrust");
                //connection string - oracle
                Oracle_ConnectionStringUser = GetXmlReader(PathXML, "dbConfigs/ConnectionString/Oracle_ConnectionStringUser");

                #region "receives general delegations"
                #region "delegates - SQLServer"
                //captura os eventos da conexão do sql-server
                cnnSQLServer.OnConnect += new ConnectionSQLServer.ConnectDelegate(cnn_OnConnect);
                cnnSQLServer.OnCancel += new ConnectionSQLServer.CancelDelegate(cnn_OnCancel);
                cnnSQLServer.OnError += new ConnectionSQLServer.ErrorDelegate(cnn_OnError);
                cnnSQLServer.OnHelp += new ConnectionSQLServer.HelpDelegate(cnn_OnHelp);
                cnnSQLServer.OnOption += new ConnectionSQLServer.OptionsDelegate(cnn_OnOption);
                cnnSQLServer.OnChangeAuthentication += new ConnectionSQLServer.AuthenticationDelegate(cnn_OnChangeAuthentication);

                #endregion

                #region "delegates - Oracle"
                //captura os eventos da conexão do oracle
                cnnOracle.OnConnect += new ConnectionOracle.ConnectDelegate(cnn_OnConnect);
                cnnOracle.OnCancel += new ConnectionOracle.CancelDelegate(cnn_OnCancel);
                cnnOracle.OnError += new ConnectionOracle.ErrorDelegate(cnn_OnError);
                cnnOracle.OnHelp += new ConnectionOracle.HelpDelegate(cnn_OnHelp);
                cnnOracle.OnOption += new ConnectionOracle.OptionsDelegate(cnn_OnOption);

                #endregion
                #endregion
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion



        #region "capture events"
        /// <summary>
        /// capture the event cnnSQLServer connect button
        /// </summary>
        void cnn_OnConnect(Object sender, EventArgs e)
        {
            String _cnnString = "";
            try
            {
                if (cboDatabaseType.Text.ToLower() == "sqlserver")
                {
                    if (cnnSQLServer.GetAuthenticationType() == 1)
                    {
                        _cnnString = String.Format(SQLServer_ConnectionStringUser,
                            cnnSQLServer.GetServerName(),
                            cnnSQLServer.GetServerName(),
                            cnnSQLServer.GetConnectionTimeOut().ToString(),
                            cnnSQLServer.GetDefaultDatabase(),
                            cnnSQLServer.GetUserName(),
                            password.Decrypt(cnnSQLServer.GetPassword(), "SQLSERVERBuilder"));
                    }
                    else
                    {
                        _cnnString = String.Format(SQLServer_ConnectionStringTrust,
                            cnnSQLServer.GetServerName(),
                            cnnSQLServer.GetServerName(),
                            cnnSQLServer.GetConnectionTimeOut().ToString(),
                            cnnSQLServer.GetDefaultDatabase());
                    }

                    clDb = new ik.DB.SQLServer.clDbSQL();
                    ((ik.DB.SQLServer.clDbSQL)clDb).GetCnn(_cnnString);

                    //stating that triggers the event is connected
                    if (OnConnect != null)
                        OnConnect(ik.DB.enSGBD.SQLServer);


                    //write new server name (or reorder)
                    WriteXml(PathXML, "dbConfigs/ConnectionString/SQLServer_LastServersNames", cnnSQLServer.GetServerName(), "|", true);
                    //cnnSQLServer.SetServerNames(GetXmlReader(PathXML, "SQLServer_LastServersNames").Split('|'));
                    cnnSQLServer.SetServerNames(GetXmlReader(PathXML, "dbConfigs/ConnectionString/SQLServer_LastServersNames").Split('|'));

                    //write or remove rememberit
                    if (cnnSQLServer.GetRememberIt() && cnnSQLServer.GetAuthenticationType() == 1)
                    {
                        WriteRememberIt(PathXML, cnnSQLServer.GetServerName(), cnnSQLServer.GetUserName(), cnnSQLServer.GetPassword());
                    }
                    else if (cnnSQLServer.GetAuthenticationType() == 1)
                    {
                        RemoveRememberIt(PathXML, cnnSQLServer.GetServerName());
                    }
                } // END SQLServer connection

                else if (cboDatabaseType.Text.ToLower() == "oracle")
                {
                    //"gestaoparcerias", "p4rc3r1455", "gestaoparchmg"
                    clDb = new ik.DB.Oracle.clDbORA();
                    ((ik.DB.Oracle.clDbORA)clDb).GetCnn(
                        cnnOracle.GetUserName(),
                        ik.FunctionsDB.clFunctionsDB.CrypTo2(password.Decrypt(cnnOracle.GetPassword(), "ORACLEBuilder"), ik.FunctionsDB.enEncryption.Encriptar),
                        cnnOracle.GetServerName(), 0);

                    //stating that triggers the event is connected
                    if (OnConnect != null)
                        OnConnect(ik.DB.enSGBD.Oracle);
                }


            }
            catch (Exception ex)
            {
                if (OnError != null)
                    OnError(ex);
                else
                    MessageBox.Show(ex.Message, "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        void cnn_OnCancel(object sender, EventArgs e)
        {
            MessageBox.Show("click cancel");
        }
        void cnn_OnHelp()
        {
            MessageBox.Show("click help");
        }
        void cnn_OnOption()
        {
            //MessageBox.Show("click options");
        }
        void cnn_OnError(Exception e)
        {
            MessageBox.Show(e.Message, "Evento erro");
        }
        void cnn_OnChangeAuthentication(Int32 authentication)
        {
            try
            {
                if (authentication == 1)
                {
                    LoadAuthentication(PathXML, cnnSQLServer.GetServerName());
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Erro", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        #endregion

        #region "SetComboInformation"
        /// <summary>
        /// fill a combobox
        /// </summary>
        /// <param name="cbo">ComboBox</param>
        /// <param name="options">List<String></param>
        /// /// <param name="selectedindex">SelectedIndex (-1 to null)<String></param>
        public void SetComboInformation(ComboBox cbo, List<String> options, Int32 selectedindex)
        {
            try
            {
                cbo.Items.Clear();
                foreach (String s in options)
                    cbo.Items.Add(s);
                if (cbo.Items.Count > 0 && selectedindex > -1)
                    cbo.SelectedIndex = selectedindex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "cboDatabaseType_SelectedIndexChanged"
        /// <summary>
        /// change ComboBox information
        /// </summary>
        private void cboDatabaseType_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                foreach (TabPage tp in tabSGBD.TabPages)
                    TabVisible(tabSGBD, tp, enVisible.Invisible);

                foreach (TabPage p in PagesSGBD)
                    if (p.Name.ToLower().Contains(((ComboBox)sender).Text.ToLower()))
                        TabVisible(tabSGBD, p, enVisible.Visible);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "TabVisible"
        /// <summary>
        /// enable/disable TabPage
        /// </summary>
        /// <param name="tp">TabPage</param>
        /// <param name="vs">enum enVisivel</param>
        public void TabVisible(TabControl ctr, TabPage tp, enVisible vs)
        {
            try
            {
                if (ctr.TabPages.Contains(tp) && vs == enVisible.Visible)
                    return;

                if (vs == enVisible.Visible)
                    ctr.TabPages.Add(tp);
                else
                    ctr.TabPages.Remove(tp);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "EnableControls"
        /// <summary>
        /// enable/disable controls
        /// </summary>
        /// <param name="ctls">TabPage.Controls</param>
        /// <param name="enable">Boolean</param>
        private static void EnableControls(Control.ControlCollection ctls, Boolean enable)
        {
            try
            {
                foreach (Control ctl in ctls)
                {
                    ctl.Enabled = enable;
                    EnableControls(ctl.Controls, enable);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        #endregion

        #region "returns the type of SGBDs <String>List"
        /// <summary>
        /// load list SGBDs
        /// </summary>
        /// <param name="path">path XML</param>
        /// <returns>List Strings</returns>
        private List<String> GetSGBD(String path)
        {
            try
            {
                SGBD = new List<String>();
                foreach (String s in GetXmlReader(path, "dbConfigs/SGBD/SGBDtype").Split('|'))
                    SGBD.Add(s);
                return SGBD;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "GetXmlReader"
        /// <summary>
        /// XML reader
        /// </summary>
        /// <param name="xmlPath">path</param>
        /// <param name="nodeName">read attribute (qualified)</param>
        /// <returns>String</returns>
        private String GetXmlReader(String xmlPath, String nodeName)
        {
            try
            {
                return GetXmlReader(xmlPath, nodeName, "value");
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// XML reader
        /// </summary>
        /// <param name="xmlPath">path</param>
        /// <param name="nodeName">read attribute (qualified)</param>
        /// <returns>String</returns>
        private String GetXmlReader(String xmlPath, String nodeName, String attribute)
        {
            try
            {
                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(xmlPath);
                XmlNode node = xmlDoc.SelectSingleNode(nodeName);
                return node.Attributes[attribute].Value;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// XML reader 2
        /// </summary>
        /// <param name="xmlPath">path</param>
        /// <param name="attibute">read attribute</param>
        /// <returns>String</returns>
        private String GetXmlReader2(String xmlPath, String attibute)
        {
            System.Xml.XmlTextReader _xml = null;
            try
            {
                _xml = new System.Xml.XmlTextReader(xmlPath);
                while (_xml.Read())
                    if (_xml.NodeType == XmlNodeType.Element)
                        if (_xml.Name.ToLower() == attibute.ToLower())
                            return _xml.GetAttribute("value");
                return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_xml != null)
                    _xml.Close();
            }
        }
        #endregion

        #region "WriteXml"
        /// <summary>
        /// write element tag in XML
        /// </summary>
        /// <param name="pathXML">file path XML</param>
        /// <param name="nodeName">node name</param>
        /// <param name="value">value to write</param>
        /// <param name="split">split separator</param>
        /// <param name="append">append or override</param>
        private void WriteXml(String pathXML, String nodeName, String value, String split, Boolean append)
        {
            try
            {
                Int32 i = 0;
                String _servers = value;
                if (append)
                {
                    foreach (String s in GetXmlReader(pathXML, nodeName).Split(Convert.ToChar(split)))
                    {
                        if (s.ToLower().Equals(value.ToLower()))
                            continue;
                        i++;
                        _servers += split + s;

                        if (i.Equals(10))
                            break;
                    }
                }

                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(pathXML);
                XmlNode node = xmlDoc.SelectSingleNode(nodeName);
                node.Attributes[0].Value = _servers;
                xmlDoc.Save(pathXML);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "WriteRememberIt"
        /// <summary>
        /// write on xml file a rememberit
        /// </summary>
        /// <param name="pathXML">path xml</param>
        /// <param name="servername">server name</param>
        /// <param name="user">user name</param>
        /// <param name="password">password</param>
        private void WriteRememberIt(String pathXML, String servername, String user, String password)
        {
            try
            {
                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(pathXML);
                XmlNodeList nodes = xmlDoc.SelectNodes("dbConfigs/ConnectionString/SQLServer_RememberIt");
                foreach (XmlNode nd in nodes)
                {
                    if (nd.Attributes["servername"].Value.ToString().ToLower() == servername.ToLower())
                    {
                        nd.Attributes["servername"].Value = servername;
                        nd.Attributes["user"].Value = user;
                        nd.Attributes["password"].Value = password;
                        xmlDoc.Save(pathXML);
                        return;
                    }
                }

                //not exists SQLServer_RememberIt -> servername in xml
                XmlNode linha = xmlDoc.CreateElement("SQLServer_RememberIt");

                //create attributes
                XmlAttribute att = null;
                att = xmlDoc.CreateAttribute("servername");
                att.Value = servername;
                linha.Attributes.Append(att);

                att = xmlDoc.CreateAttribute("user");
                att.Value = user;
                linha.Attributes.Append(att);

                att = xmlDoc.CreateAttribute("password");
                att.Value = password;
                linha.Attributes.Append(att);

                #region "demo criar elementos"
                //XmlNode Id = xmlDoc.CreateElement("id");
                //XmlNode Nome = xmlDoc.CreateElement("nome");

                //Id.InnerText = "11";
                //Nome.InnerText = "Ivan M. Kruger";

                //linha.AppendChild(Id);
                //linha.AppendChild(Nome);
                #endregion

                //local to save on xml
                xmlDoc.SelectSingleNode("/dbConfigs/ConnectionString").AppendChild(linha);
                xmlDoc.Save(pathXML);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "RemoveRememberIt"
        /// <summary>
        /// remove rememberit
        /// </summary>
        /// <param name="pathXML">path xml</param>
        /// <param name="servername">server name</param>
        private void RemoveRememberIt(String pathXML, String servername)
        {
            try
            {
                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(pathXML);

                XmlNode _node = xmlDoc.SelectSingleNode("//SQLServer_RememberIt[@servername='" + servername + "']");
                if (_node != null)
                {
                    _node.ParentNode.RemoveChild(_node);
                    xmlDoc.Save(pathXML);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region "LoadAuthentication"
        /// <summary>
        /// load rememberit credentials
        /// </summary>
        /// <param name="pathXML">path xml</param>
        /// <param name="servername">server name</param>
        private void LoadAuthentication(String pathXML, String servername)
        {
            try
            {
                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(pathXML);
                XmlNodeList nodes = xmlDoc.SelectNodes("dbConfigs/ConnectionString/SQLServer_RememberIt");
                foreach (XmlNode nd in nodes)
                {
                    if (nd.Attributes["servername"].Value.ToString().ToLower() == servername.ToLower())
                    {
                        cnnSQLServer.SetUserName(nd.Attributes["user"].Value);
                        cnnSQLServer.SetPassword(nd.Attributes["password"].Value);
                        cnnSQLServer.SetRememberIt(true);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}
